﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;

namespace RESP_IIWaterAssessment
{
    public partial class DrainageStateFormReport : Form
    {
        public DrainageStateFormReport()
        {
            InitializeComponent();
        }

        int avpID, regID, year;

        private void DrainageStateFormReport_Load(object sender, EventArgs e)
        {
            cbRegion.SelectedIndexChanged -= cbRegion_SelectedIndexChanged;
            cbRegion.DataSource = UtilClass.FillCBRegions();
            cbRegion.DisplayMember = "Name";
            cbRegion.ValueMember = "ID";

            if (InformationClass.isAdmin)
            {
                cbRegion.SelectedIndex = -1;
                cbRegion.Text = string.Empty;
                cbRegion.SelectedIndexChanged += new EventHandler(cbRegion_SelectedIndexChanged);
            }
            else
            {
                chbRegion.Checked = true;
                cbRegion.Enabled = chbRegion.Enabled = false;
                regID = InformationClass.regionID;
                cbRegion.SelectedIndexChanged += new EventHandler(cbRegion_SelectedIndexChanged);
                cbRegion.SelectedValue = regID;
                cbRegion_SelectedIndexChanged(cbRegion, null);
                cbAVP.Enabled = true;
                cbAVP.Text = string.Empty;
                //toolStrip1.Visible = false;
            }

            for (int i = 1991; i < 2100; i++)
            {
                cbYearDr.Items.Add(i);
            }
            cbYearDr.SelectedIndex = cbYearDr.FindStringExact(DateTime.Now.Year.ToString());
            year = Convert.ToInt32(cbYearDr.Text);
        }

        private void cbRegion_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cbRegion.SelectedIndex >= 0)
            {
                cbAVP.Enabled = true;
                regID = Convert.ToInt32(cbRegion.SelectedValue);
                cbAVP.SelectedIndexChanged -= cbAVP_SelectedIndexChanged;
                cbAVP.DataSource = UtilClass.FillAVP(regID);
                cbAVP.ValueMember = "ID";
                cbAVP.DisplayMember = "Name";
                cbAVP.SelectedIndex = -1;
                cbAVP.Text = string.Empty;
                cbAVP.SelectedIndexChanged += new EventHandler(cbAVP_SelectedIndexChanged);
            }
        }

        private void cbAVP_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cbAVP.SelectedIndex >= 0)
            {
                avpID = Convert.ToInt32(cbAVP.SelectedValue);
            }
            else
                avpID = 0;
        }

        private void cbYearDr_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cbYearDr.SelectedIndex >= 0)
            {
                year = Convert.ToInt32(cbYearDr.Text);
            }
        }

        private void buttonShow_Click(object sender, EventArgs e)
        {
            int? rID, aID, y;

            if (chbRegion.Checked && regID > 0 && cbRegion.SelectedIndex >= 0)
                rID = regID;
            else
                rID = null;

            if (chbAVP.Checked && avpID > 0 && cbAVP.SelectedIndex >= 0)
                aID = avpID;
            else
                aID = null;

            if (chbYear.Checked && cbYearDr.SelectedIndex >= 0 && year > 0)
                y = year;
            else
                y = null;

            dgvDrainageStateReport.DataSource = DBClass.RESPContext.DrainageStateReport(rID, aID, y);

            if (InformationClass.Lang)
            {
                dgvDrainageStateReport.Columns["Name"].HeaderText = "АВП";
                dgvDrainageStateReport.Columns["Area"].HeaderText = "Орошаемая площадь тыс. га";
                dgvDrainageStateReport.Columns["DrArea"].HeaderText = "Площадь обеспеченая дренажем тыс. га";
                dgvDrainageStateReport.Columns["FullLength"].HeaderText = "Общая протяж.КДС,км";
                dgvDrainageStateReport.Columns["OpenLength"].HeaderText = "Открытая протяженность";
                dgvDrainageStateReport.Columns["CloseLength"].HeaderText = "Закрытая протяженность";
                dgvDrainageStateReport.Columns["ClearKDS"].HeaderText = "Требуется очист.в/х КДС";
                dgvDrainageStateReport.Columns["RenovationKDS"].HeaderText = "Требуется рекон. КДС";
                dgvDrainageStateReport.Columns["CurrentRepair"].HeaderText = "Требуется текущ. ремонт";
                dgvDrainageStateReport.Columns["CapitalRepair"].HeaderText = "Требуется кап. ремонт";
                dgvDrainageStateReport.Columns["IrrArea"].HeaderText = "Удельная протяж. в/х КДС,пм/га орош.площадь";
                dgvDrainageStateReport.Columns["DrainageArea"].HeaderText = "Удельная протяж. в/х КДС,пм/га дрениров.площадь";
                dgvDrainageStateReport.Columns["MeliorativeQuantity"].HeaderText = "Мелиоративные скважины";
                dgvDrainageStateReport.Columns["IrrigationQuantity"].HeaderText = "Скважины на орошение";
            }
            else
            {
                dgvDrainageStateReport.Columns["Name"].HeaderText = "СИУ номи";
                dgvDrainageStateReport.Columns["Area"].HeaderText = "Жами суғориладиган майдон, минг га.";
                dgvDrainageStateReport.Columns["DrArea"].HeaderText = "Дренаж тизими билан таъминланган майдон,минг га";
                dgvDrainageStateReport.Columns["FullLength"].HeaderText = "Умумий узунлиги КДС, км";
                dgvDrainageStateReport.Columns["OpenLength"].HeaderText = "Жумладан очиқ";
                dgvDrainageStateReport.Columns["CloseLength"].HeaderText = "Жумладан ёпиқ";
                dgvDrainageStateReport.Columns["ClearKDS"].HeaderText = "х/и КДТни тозалаш ";
                dgvDrainageStateReport.Columns["RenovationKDS"].HeaderText = "КДТни қайта тиклаш";
                dgvDrainageStateReport.Columns["CurrentRepair"].HeaderText = "х/и КДТни тозалаш ";
                dgvDrainageStateReport.Columns["CapitalRepair"].HeaderText = "КДТни қайта тиклаш";
                dgvDrainageStateReport.Columns["IrrArea"].HeaderText = "х/и КДТнинг солиштирма узунлиги, пм/га Суғорила-диган майдон";
                dgvDrainageStateReport.Columns["DrainageArea"].HeaderText = "х/и КДТнинг солиштирма узунлиги, пм/га Дренаж қилинади-ган майдон";
                dgvDrainageStateReport.Columns["MeliorativeQuantity"].HeaderText = "Мелиоратив қудуқлар";
                dgvDrainageStateReport.Columns["IrrigationQuantity"].HeaderText = "Суғориш қудуқлари";
            }
            for (int i = 0; i < dgvDrainageStateReport.RowCount; i++)
            {
                dgvDrainageStateReport.Rows[i].HeaderCell.Value = (i + 1).ToString();
            }
        }

        Sheets excelsheets;
        Worksheet excelworksheet;
        Microsoft.Office.Interop.Excel.Application excelapp;
        Workbook excelappworkbook;

        private void buttonExcel_Click(object sender, EventArgs e)
        {
            if (dgvDrainageStateReport.RowCount > 0)
            {
                MessageBox.Show(msgClass.msgWaiting);
                GetReport();

                excelworksheet.Select(1);
                excelapp.Visible = true;
            }
        }


        private void GetReport()
        {
            excelapp = new Microsoft.Office.Interop.Excel.Application();

            if (InformationClass.Lang)
                excelapp.Workbooks.Open(System.Windows.Forms.Application.StartupPath + @"\Templates\i9r11.xls", false, true, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            else
                excelapp.Workbooks.Open(System.Windows.Forms.Application.StartupPath + @"\Templates\i9r11uz.xls", false, true, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            excelappworkbook = excelapp.Workbooks[1];
            excelsheets = excelappworkbook.Worksheets;// .Worksheets[0]; //Sheets 
            excelworksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelsheets.get_Item(1);

            excelworksheet.Cells.NumberFormat = "@";

            if (chbYear.Checked)
                excelworksheet.Cells[6, 2] = "Год:" + cbYearDr.Text;

            if (chbRegion.Checked)
                excelworksheet.Cells[4, 3] = cbRegion.Text;

            object[,] objData = new Object[dgvDrainageStateReport.Rows.Count, dgvDrainageStateReport.Columns.Count + 1];
            for (int i = 0; i < dgvDrainageStateReport.Rows.Count; i++)
            {
                objData[i, 0] = i+1;
                for (int j = 0; j < dgvDrainageStateReport.Columns.Count; j++)
                    objData[i, j + 1] = dgvDrainageStateReport[j, i].FormattedValue;
            }

            Range rng = excelworksheet.get_Range(excelworksheet.Cells[10, 2], excelworksheet.Cells[dgvDrainageStateReport.Rows.Count+9, dgvDrainageStateReport.Columns.Count + 2]);
            rng.Value2 = objData;
            rng.Borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlContinuous;
            rng.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
            rng.Borders[XlBordersIndex.xlEdgeRight].LineStyle = XlLineStyle.xlContinuous;
            rng.Borders[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlContinuous;
            rng.Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlContinuous;
            rng.Borders[XlBordersIndex.xlInsideVertical].LineStyle = XlLineStyle.xlContinuous;

            excelworksheet.Columns.AutoFit();
            excelworksheet.Rows.AutoFit();
        }

        //private void GetReport()
        //{
        //    excelapp = new Microsoft.Office.Interop.Excel.Application();

        //    if (InformationClass.Lang)
        //        excelapp.Workbooks.Open(System.Windows.Forms.Application.StartupPath + @"\Templates\i9r11.xls", false, true, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        //    else
        //        excelapp.Workbooks.Open(System.Windows.Forms.Application.StartupPath + @"\Templates\i9r11uz.xls", false, true, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

        //    excelappworkbook = excelapp.Workbooks[1];
        //    excelsheets = excelappworkbook.Worksheets;// .Worksheets[0]; //Sheets 
        //    excelworksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelsheets.get_Item(1);

        //    excelworksheet.Cells.NumberFormat = "@";

        //    if (chbYear.Checked)
        //        excelworksheet.Cells[6, 2] = "Период: __ квартал " + cbYearDr.Text + " года";

        //    if (chbRegion.Checked)
        //        excelworksheet.Cells[4, 2] = cbRegion.Text;

        //    for (int i = 0; i < dgvDrainageStateReport.Rows.Count; i++)
        //    {
        //        for (int j = 0; j < dgvDrainageStateReport.Columns.Count; j++)
        //        {
        //            excelworksheet.Cells[i + 10, dgvDrainageStateReport.Columns[j].DisplayIndex + 3] = dgvDrainageStateReport[j, i].FormattedValue;
        //            addBorder(excelworksheet.Cells[i + 10, dgvDrainageStateReport.Columns[j].DisplayIndex + 3]);
        //        }
        //        excelworksheet.Cells[i + 10, 2] = i + 1;
        //        addBorder(excelworksheet.Cells[i + 10, 2]);
        //    }

        //    excelworksheet.Columns.AutoFit();
        //    excelworksheet.Rows.AutoFit();
        //}

        private void addBorder(object p)
        {
            Range tmpRange = excelworksheet.get_Range(p, p);
            tmpRange.Borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlContinuous;
            tmpRange.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
            tmpRange.Borders[XlBordersIndex.xlEdgeRight].LineStyle = XlLineStyle.xlContinuous;
            tmpRange.Borders[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlContinuous;
        }

        private void buttonPrint_Click(object sender, EventArgs e)
        {
            if (dgvDrainageStateReport.RowCount > 0)
                PrintDGV.Print_DataGridView(dgvDrainageStateReport);
        }

        private void dgvDrainageStateReport_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
        {
            if (e.ColumnIndex == dgvDrainageStateReport.Columns["IrrArea"].Index)
                e.Value = ((decimal)e.Value).ToString("N2");
            if (e.ColumnIndex == dgvDrainageStateReport.Columns["DrainageArea"].Index)
                e.Value = ((decimal)e.Value).ToString("N2");
        }
    }
}
